package DaosImpl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

import Daos.StaticsCountDao;
import Beans.DataBase;
import Beans.StaticsInfo;

public class StaticsCountDaoImpl implements StaticsCountDao{

	@Override
	public ArrayList queryAllStaticsCount() throws Exception {
		Connection conn = null;
        Statement stat1 = null;
        Statement stat2 = null;
        Statement stat3 = null;
        ArrayList staticsList = new ArrayList();
        int r_id = 0;
        ApplicationContext context = new FileSystemXmlApplicationContext("D:\\MyEcilipseWorkSpace\\HotelSystem\\src\\applicationContext.xml");
        DataBase dataBase = (DataBase)context.getBean("dataBase");
        String url = dataBase.getUrl()+dataBase.getDataBaseName()+dataBase.getAccount()+dataBase.getPwd();
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	//String url =  "jdbc:sqlserver://localhost:1433;" +
        			  //"databaseName=management_system;user=sa;password=Zls062813;";
			conn = DriverManager.getConnection(url);
			String sql1 = "select count(*),Client.client_id from Client,Order_Form where Client.client_id=Order_Form.client_id group by(Client.client_id);";
			String sql2 = "select sum(rt_rent_money),client_id from  Room_Type,Room,Order_Form,Open_Room where Order_Form.of_id=Open_Room.of_id and"+
					" Open_Room.r_id=Room.r_id and Room.rt_id=Room_Type.rt_id group by(Order_Form.client_id);";
			stat1 = conn.createStatement();
			stat2 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			stat3 = conn.createStatement();
			ResultSet rs1 = stat1.executeQuery(sql1);
			ResultSet rs2 = stat2.executeQuery(sql2);
            while(rs1.next()){
            	StaticsInfo staticsInfo = new StaticsInfo();
            	staticsInfo.setCount(rs1.getInt(1));
            	staticsInfo.setId(rs1.getString(2));
            	String sql3 = "select client_name from Client where client_id='"+rs1.getString(2)+"';";
            	ResultSet rs3 = stat3.executeQuery(sql3);
            	rs3.next();
            	staticsInfo.setName(rs3.getString(1));
            	while(rs2.next())
            	{
            		if(rs2.getString(2).equals(rs1.getString(2))){
            			staticsInfo.setMoney(Float.parseFloat(rs2.getString(1)));
            			break;
            		}
            	}
            	rs2.beforeFirst();
            	staticsList.add(staticsInfo);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try{
                if(conn!=null){
                    conn.close();
                }
                if(stat1!=null){
                	stat1.close();
                }
            }catch (Exception ex){
            	ex.printStackTrace();
            }
        }
        return staticsList;
	}
	
}
